package com.zhou;


import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;

public class JParserTest {
    public static void main(String[] args) throws JSQLParserException {
        String sql = "    select\n" +
                "        TT.paramCode,\n" +
                "        (case \n" +
                "            when dp.precision=0 then TT.avgValue \n" +
                "            else round(TT.avgValue,\n" +
                "            dp.precision) \n" +
                "        end)as\n" +
                "        avgValue,\n" +
                "        mq.qtypeName,\n" +
                "        sd.keyName,\n" +
                "        dp.paramName \n" +
                "    from\n" +
                "        (\n" +
                "        select\n" +
                "            222 as paramCode ,\n" +
                "            avg(miv.validvalue)as avgValue,\n" +
                "            111 as siteCode\n" +
                "        from\n" +
                "            moncenter_ischecked_view miv \n" +
                "        where\n" +
                "            siteCode =111 \n" +
                "            and paramCode =222\n" +
                "            and to_char(miv.datatime,'YYYY-MM-DD') >= 11 \n" +
                "            and to_char(miv.datatime,'YYYY-MM-DD') <11\n" +
                "            and to_char(miv.datatime,'YYYY-MM')=11\n" +
                "        )TT\n" +
                "    LEFT JOIN\n" +
                "        dev_param dp\n" +
                "            on TT.paramCode = dp.paramCode\n" +
                "    left join\n" +
                "        site_infor si \n" +
                "            on tt.siteCode = si.siteCode\n" +
                "    left join\n" +
                "        moncenter_qualitytypecfg mq\n" +
                "            on dp.id = mq.paramid \n" +
                "            and TT.avgValue <= mq.MaxValue \n" +
                "            and TT.avgValue > mq.minvalue \n" +
                "            and mq.siteType=si.siteType\n" +
                "    left join\n" +
                "        system_dictionary sd\n" +
                "            on mq.qtypeName = sd.keyvalue \n" +
                "            and sd.dictype ='水质类别'\n";
        //String sql = "    select\n" +
        //        "        * \n" +
        //        "    from\n" +
        //        "        (\n" +
        //        "    select\n" +
        //        "            tt.*,\n" +
        //        "            rownum as rowno \n" +
        //        "        from\n" +
        //        "            (\n" +
        //        "    select\n" +
    //        "   ps.id,\n" +
    //        "            ps.userid,\n" +
    //        "            ps.scoreitemid,\n" +
    //        "            ps.score,\n" +
    //        "            ps.recorduserid,\n" +
    //        "            ps.isvalid,\n" +
    //        "            ps.siteCode,\n" +
    //        "      ps.recorddate,\n" +
    //        "            psi.itemtype,\n" +
    //        "            psi.itemcontent,\n" +
    //        "            psi.describe,\n" +
    //        "            to_char(ps.recorddate,\n" +
    //        "            'yyyy-mm')as recordDateStr \n" +
    //        "        from\n" +
    //        "            extpms_score ps\n" +
    //        "        join\n" +
    //        "            pms_scoreitem psi\n" +
    //        "                on ps.scoreitemid = psi.id\n" +
    //        "        where\n" +
    //        "            1=1\n" +
    //        "            and psi.itemType LIKE CONCAT(CONCAT('%',1),'%')\n" +
    //        "            and ps.siteCode =1   \n" +
    //        "            and to_char(ps.recorddate,'yyyy-mm') = 1   \n" +
    //        "        )tt  \n" +
    //        "    where\n" +
    //        "        rownum <= 11+1\n" +
    //        "   )table_temp \n" +
    //        "where\n" +
        //        "    table_temp.rowno > 11\n";
        long current = System.currentTimeMillis();
        Statement statement = CCJSqlParserUtil.parse(replaceBeforeConvert(sql));
        System.out.println("statement = " + statement);
        MyJSqlVisitor myJSqlVisitor = new MyJSqlVisitor(statement);
        statement.accept(myJSqlVisitor);
        if (statement != myJSqlVisitor.getStatement()) {
            statement = myJSqlVisitor.getStatement();
            statement.accept(myJSqlVisitor);
        }
        System.out.println("\n statement = " + replaceAfterConvert(statement.toString()));
        long now = System.currentTimeMillis();
        System.out.println("sql 转换时间：" + (now - current) + "ms");

    }

//    public static void main(String[] args) throws JSQLParserException {
//        Expression expression = CCJSqlParserUtil.parseExpression("to_char(222)");
//        System.out.println("expression.getASTNode().jjtGetValue() = " + expression.getASTNode().jjtGetValue());
//    }

    private static String replaceBeforeConvert(String sql) {
        return sql.replaceAll("(?i)minvalue", "`minvalue`")
                .replaceAll("(?i)maxvalue", "`maxvalue`");
    }

    private static String replaceAfterConvert(String sql) {
        return sql.replaceAll("\\(SELECT rownum - 1 rn FROM dual CONNECT BY rownum", "(select id - 1 from sequence order by id limit")
                .replaceAll("(?i)from dual", "");
    }

}
